package com.gec.dao.impl;

import com.gec.dao.JobDao;
import com.gec.domain.Job;
import com.gec.utils.JDBCUtils;
import com.gec.utils.PageModel;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class JobDaoImpl implements JobDao {
    //添加职位
    @Override
    public void addJob(Job job) {
        String sql="INSERT INTO job_inf (id,NAME,remark)VALUES(?,?,?)";
        Object[]params={job.getId(),job.getName(),job.getRemark()};
        try {
            JDBCUtils.executeUpdate(sql,params);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),null);
        }
    }

    //修改职位
    @Override
    public void updataJob(Job job) {
        String sql="UPDATE job_inf SET NAME=?, remark=? WHERE id=?";
        Object[]params={job.getName(),job.getRemark(),job.getId()};
        try {
            JDBCUtils.executeUpdate(sql,params);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),null);
        }
    }

    //删除职位
    @Override
    public void deletejob(int id) {
        String sql="DELETE FROM job_inf WHERE id=?";
        Object[]params={id};
        try {
            JDBCUtils.executeUpdate(sql,params);
        }catch (Exception e){
            e.printStackTrace();
        } finally{
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),null);
        }
    }

    //查看职位
    @Override
    public List<Job> findAllJob(Job job, PageModel model) {
        List<Job>jobs=new ArrayList<>();

        //动态查询
        StringBuffer sql=new StringBuffer("select * from job_inf where 1=1 ");
        List<Object>objects=new ArrayList<>();

        ResultSet rs=null;
        try {
            //防止空指针异常
            if(job !=null && !"".equals(job.getName()) && job.getName() !=null){
                sql.append(" and name like ?");
                objects.add("%"+job.getName()+"%");
            }
            //分页查询
            sql.append(" limit ").append(model.getStartRow()).append(",").append(PageModel.pageSize);

            //形成数组
            Object[] params = objects.toArray(new Object[objects.size()]);

            rs=JDBCUtils.executeQuery(sql.toString(),params);

            while(rs.next()){
                Job job1=new Job();
                copy(rs,job1);
                jobs.add(job1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),null);
        }
        return jobs;
    }

    @Override
    public int getTotalJobCount(Job job) {
        StringBuffer sql=new StringBuffer("SELECT COUNT(0) FROM job_inf  where 1=1 ");
        List<Object>objects=new ArrayList<>();
        ResultSet rs=null;

        try {
            if(job !=null && !"".equals(job.getName()) && job.getName()!=null){
                sql.append(" and name like ?");
                objects.add("%"+job.getName()+"%");
            }
            Object[]params=objects.toArray(new Object[objects.size()]);
            rs=JDBCUtils.executeQuery(sql.toString(),params);
            while (rs.next()){
                return rs.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),rs);
        }
        return 0;
    }

    @Override
    public Job findJobById(int id) {
        String sql="SELECT * FROM job_inf WHERE id=?";
        Object[]params={id};
        ResultSet rs=null;
        try {
            rs=JDBCUtils.executeQuery(sql,params);
            while(rs.next()){
                Job job1=new Job();
                copy(rs,job1);
                return job1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),null);
        }
        return null;
    }

    @Override
    public List<Job> findJob(Job job) {
        List<Job>jobs=new ArrayList<>();
        String sql="select * from job_inf";
        ResultSet rs=null;
        try{
            rs=JDBCUtils.executeQuery(sql);
            while(rs.next()){
                Job job1=new Job();
                copy(rs,job1);
                jobs.add(job1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),rs);
        }
        return jobs;
    }


    private void copy(ResultSet rs, Job job1) {
        try {
            job1.setId(rs.getInt("id"));
            job1.setName(rs.getString("name"));
            job1.setRemark(rs.getString("remark"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
